using System;
using System.Data;
using System.Collections.Generic;
namespace eTMS.DAO
{
    /********************************BEGIN KhachHangDAO****************************************/
    ///<summary>
    /// <para>Author: Auto generated class by CSharp Code Generator v2.0</para>
    /// <para>Date:26/09/2011 47:10:34</para>
    /// <para>Class Name: KhachHangDAO</para>
    /// <para>Description: Class access to table KhachHang in database. Class for coder code here</para>
    ///</summary>


    public partial class KhachHangDAO
    {
        public DataSet SelectAllDSByUserFilter(int? User_ID, bool? IsFinished, int ListType, DateTime? TuNgay, DateTime? DenNgay, KhachHangEntity oFilter)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT  DISTINCT    KhachHang.*, CaHoc.TenCa, NganhHoc.TenNganhHoc, TinhThanh.TenTinhThanh, QuanHuyen.TenQuanHuyen, ThanhPhan.TenThanhPhan, NguonTin.TenNguonTin, 
                                    TrangThaiKH.TenTrangThaiKH,(SELECT COUNT(LogKhachHang.KhachHang_ID)as CountLog FROM LogKhachHang WHERE LogKhachHang.KhachHang_ID=KhachHang.KhachHang_ID) as CountLog
                                FROM            KhachHang LEFT JOIN
                         CaHoc ON KhachHang.CaHoc_ID = CaHoc.CaHoc_ID LEFT JOIN
                         NganhHoc ON KhachHang.NganhHoc_ID = NganhHoc.NganhHoc_ID LEFT JOIN
                         TinhThanh ON KhachHang.TinhThanh_ID = TinhThanh.TinhThanh_ID LEFT JOIN
                         QuanHuyen ON KhachHang.QuanHuyen_ID = QuanHuyen.QuanHuyen_ID LEFT JOIN
                         ThanhPhan ON KhachHang.ThanhPhan_ID = ThanhPhan.ThanhPhan_ID LEFT JOIN
                         TrangThaiKH ON KhachHang.TrangThai_ID = TrangThaiKH.TrangThaiKH_ID LEFT JOIN
                         NguonTin ON KhachHang.NguonTin_ID = NguonTin.NguonTin_ID";
            if (ListType == 2)
            {
                cmd.CommandText += @" INNER JOIN KhachHangDotThi ON KhachHang.KhachHang_ID=KhachHangDotThi.KhachHang_ID 
                                     INNER JOIN DotThiDauVao ON DotThiDauVao.DotThiDauVao_ID=KhachHangDotThi.DotThi_ID ";
            }
            string filter = "";
            if (User_ID.HasValue && User_ID > 0)
            {
                filter += " AND KhachHang.User_ID=" + User_ID.ToString();
            }
            if (IsFinished.HasValue)
            {
                filter += " AND KhachHang.KetThuc=" + (IsFinished.Value ? "1" : "0");
            }
            if (TuNgay.HasValue)
            {
                if (ListType == 1)
                {
                    //Theo ngay cap nhat
                    filter += " AND KhachHang.NgayCapNhat>=@TuNgay";
                }
                else if (ListType == 0)
                {
                    //Theo ngay nhap
                    filter += " AND KhachHang.NgayNhap>=@TuNgay";
                }
                else if (ListType == 2)
                {
                    //Theo ngay thi
                    filter += " AND DotThiDauVao.NgayThi>=@TuNgay";
                }
                else if (ListType == 3)
                {
                    //Theo ngay nhap hoc
                    filter += " AND KhachHang.NgayKetThuc>=@TuNgay";
                }
                this.CreateCommandParameter(cmd, "@TuNgay", TuNgay.Value);
            }
            if (DenNgay.HasValue)
            {
                if (ListType == 1)
                {
                    filter += " AND KhachHang.NgayCapNhat<=@DenNgay";
                }
                else if (ListType == 0)
                {
                    filter += " AND KhachHang.NgayNhap<=@DenNgay";
                }
                else if (ListType == 2)
                {
                    filter += " AND DotThiDauVao.NgayThi<=@DenNgay";
                }
                else if (ListType == 3)
                {
                    //Theo ngay nhap hoc
                    filter += " AND KhachHang.NgayKetThuc<=@DenNgay";
                }
                this.CreateCommandParameter(cmd, "@DenNgay", DenNgay.Value);
            }
            if (oFilter != null)
            {
                string st = GetFilter(cmd, oFilter);
                if (st.Length > 0)
                    filter += " AND " + st;
            }
            if (filter.Length > 0)
                filter = " WHERE " + filter.Substring(4);
            cmd.CommandText += filter;
            return this.SelectDS(cmd);
        }
        private string GetFilter(IDbCommand cmd, KhachHangEntity oFilter)
        {
            string filter = "";
            if (oFilter.MaKhachHang != null && oFilter.MaKhachHang.Trim().Length > 0)
            {
                filter += " AND KhachHang.MaKhachHang=@MaKhachHang";
                this.CreateCommandParameter(cmd, "@MaKhachHang", oFilter.MaKhachHang);
            }
            if (oFilter.TenKhachHang != null && oFilter.TenKhachHang.Trim().Length > 0)
            {
                filter += " AND KhachHang.TenKhachHang Like '%'+@TenKhachHang+'%'";
                this.CreateCommandParameter(cmd, "@TenKhachHang", oFilter.TenKhachHang);
            }
            if (oFilter.CaHoc_ID > 0)
            {
                filter += " AND KhachHang.CaHoc_ID=" + oFilter.CaHoc_ID.ToString();
            }

            if (oFilter.NganhHoc_ID > 0)
            {
                filter += " AND KhachHang.NganhHoc_ID=" + oFilter.NganhHoc_ID.ToString();
            }

            if (oFilter.TinhThanh_ID > 0)
            {
                filter += " AND KhachHang.TinhThanh_ID=" + oFilter.TinhThanh_ID.ToString();
            }
            if (oFilter.QuanHuyen_ID > 0)
            {
                filter += " AND KhachHang.QuanHuyen_ID=" + oFilter.QuanHuyen_ID.ToString();
            }
            if (oFilter.DiaChi != null && oFilter.DiaChi.Trim().Length > 0)
            {
                filter += " AND KhachHang.DiaChi Like '%'+@DiaChi+'%'";
                this.CreateCommandParameter(cmd, "@DiaChi", oFilter.DiaChi);
            }
            if (oFilter.ThanhPhan_ID > 0)
            {
                filter += " AND KhachHang.ThanhPhan_ID=" + oFilter.ThanhPhan_ID.ToString();
            }
            if (oFilter.TrangThai_ID > 0)
            {
                filter += " AND KhachHang.TrangThai_ID=" + oFilter.TrangThai_ID.ToString();
            }
            if (oFilter.NguonTin_ID > 0)
            {
                filter += " AND KhachHang.NguonTin_ID=" + oFilter.NguonTin_ID.ToString();
            }
            if (filter.Length > 0)
                filter = filter.Substring(4);
            return filter;
        }
        public DataSet SelectAllDSDaNhapHoc(DateTime? TuNgay, DateTime? DenNgay, int User_ID, KhachHangEntity oFilter)
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT       KhachHang.KhachHang_ID, TenKhachHang, MaKhachHang, NgaySinh,CoDinh,DiDong, SinhVien_ID, NgayKetThuc,GioiTinh,
                                                             (SELECT        SUM(SoCoSinhVienChiTiet.SoTien) AS SoTien
                                                               FROM            SoCoSinhVienChiTiet INNER JOIN
                                                                                         SoCoSinhVien ON SoCoSinhVien.SoCoSinhVien_ID = SoCoSinhVienChiTiet.SoCoSinhVien_ID
                                                               WHERE        (SoCoSinhVien.SinhVien_ID = KhachHang.SinhVien_ID)) AS SoTien
                                FROM            KhachHang
                                WHERE KhachHang.KetThuc=1 AND KhachHang.SinhVien_ID>0";
            if (User_ID > 0)
            {
                cmd.CommandText += " AND KhachHang.User_ID=" + User_ID.ToString();
            }
            if (TuNgay.HasValue)
            {
                cmd.CommandText += " AND KhachHang.NgayKetThuc>=@TuNgay";
                this.CreateCommandParameter(cmd, "@TuNgay", TuNgay);
            }
            if (DenNgay.HasValue)
            {
                cmd.CommandText += " AND KhachHang.NgayKetThuc<=@DenNgay";
                this.CreateCommandParameter(cmd, "@DenNgay", DenNgay);
            }
            string Filter = GetFilter(cmd, oFilter);
            if (Filter.Length > 0)
            {
                cmd.CommandText += " AND " + RemoveInjection(Filter);
            }
            return this.SelectDS(cmd);
        }
        public DataSet SelectAllDSReportByTrangThai()
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT        COUNT(KhachHang.KhachHang_ID) AS SoLuong, KhachHang.TrangThai_ID, TrangThaiKH.TenTrangThaiKH
                                FROM            KhachHang LEFT JOIN
                                                         TrangThaiKH ON KhachHang.TrangThai_ID = TrangThaiKH.TrangThaiKH_ID
                                GROUP BY KhachHang.TrangThai_ID, TrangThaiKH.TenTrangThaiKH";
            return this.SelectDS(cmd);
        }
        public DataSet SelectAllDSReportByNguonTin()
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT        COUNT(KhachHang.KhachHang_ID) AS SoLuong, KhachHang.NguonTin_ID, NguonTin.TenNguonTin
                                FROM            KhachHang LEFT JOIN
                                                         NguonTin ON KhachHang.NguonTin_ID = NguonTin.NguonTin_ID
                                GROUP BY KhachHang.NguonTin_ID,  NguonTin.TenNguonTin";
            return this.SelectDS(cmd);
        }
        public DataSet SelectAllDSReportByNganhHoc()
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT        COUNT(KhachHang.KhachHang_ID) AS SoLuong, KhachHang.NganhHoc_ID, NganhHoc.TenNganhHoc
                                FROM            KhachHang LEFT JOIN
                                                         NganhHoc ON KhachHang.NganhHoc_ID = NganhHoc.NganhHoc_ID
                                GROUP BY KhachHang.NganhHoc_ID,  NganhHoc.TenNganhHoc";
            return this.SelectDS(cmd);
        }
        public DataSet SelectAllDSReportByThanhPhan()
        {
            IDbCommand cmd = this.CreateCommand();
            cmd.CommandText = @"SELECT        COUNT(KhachHang.KhachHang_ID) AS SoLuong, KhachHang.ThanhPhan_ID, ThanhPhan.TenThanhPhan
                                FROM            KhachHang LEFT JOIN
                                                         ThanhPhan ON KhachHang.ThanhPhan_ID = ThanhPhan.ThanhPhan_ID
                                GROUP BY KhachHang.ThanhPhan_ID,   ThanhPhan.TenThanhPhan";
            return this.SelectDS(cmd);
        }

    }
    /*****************************************************************************************************/
    /**********************************END KhachHangDAO*****************************************/
    /*****************************************************************************************************/
}
